package net.comm.user.db;

import org.sakaiproject.db.api.iServiceSql;


/**
 * methods for accessing user data in a database.
 */
public class UsersServiceSqlMysql implements iServiceSql
{
	/**
	 * 返回根据主键删除语句
	 */
	public String getDeleteSql()
	{
		return "delete from Users where UserID=?";
	}

	/**
	 * 返回插入语句
	 */
	public String getInsertSql()
	{
		return "insert into Users ( userID, nick, email, password, status, type, ctime, loginCnt, ltime," +
				" online, mtime, remark ) values (?,?,?,?,?,?,?,?,?,?,?,?)";
	}
	
	/**
	 * 返回根据主键修改语句
	 */
	public String getUpdateSql()
	{
		return "update Users  set nick=?, email=?, password=?, status=?," +
				" type=?, ctime=?, loginCnt=?, ltime=?, Online=?, mtime=?, remark=?" +
				"  where UserID=?";
	}

	/**
	 * 返回根据主键修改语句
	 */
	public String getUpdLtimeSql()
	{
		return "update Users  set loginCnt=?, ltime=?, Online=? " +
				"  where UserID=?";
	}

	/**
	 * 返回根据主键查询语句
	 */
	public String getbykeySql()
	{
		return "select userID, nick, email, password, status, type, ctime, loginCnt, ltime," +
				" online, mtime, remark " +
				" from Users where UserID=?";
	}
	
	/**
	 * 返回根据会员ID查询语句 
	 */
	public String getByUserIDSql()
	{
		StringBuffer sb =  new StringBuffer("select userID, nick, email, password, status, type, ctime, loginCnt, ltime," +
				" online, mtime, remarkfrom Users");
		
		sb.append(" where UserID=?  Order by Mtime desc ");
				
		return sb.toString();
	}
	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyPageSql()
	{
	
		StringBuffer sb =  new StringBuffer("select userID, nick, email, password, status, type, ctime, loginCnt, ltime," +
				" online, mtime, remark" +
				" from Users");
		
		sb.append(" where status=? Order by ctime desc ");
		sb.append(" limit ?, ?");
				
		return sb.toString();	
	}	

	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyRemindSql()
	{
	
		StringBuffer sb =  new StringBuffer("select userID, nick, email, password, status, type, ctime, loginCnt, ltime," +
				" online, mtime, remarkc" +
				" from Users");
		
		sb.append(" where ltime > ?  and ltime < ?  and email not in('')  ");
				
		return sb.toString();	

	}		
	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecSql()
	{
	
		StringBuffer sb =  new StringBuffer("select userID, nick, email, password, status, type, ctime, loginCnt, ltime," +
				" online, mtime, remark" +
				" from Users ");

		return sb.toString();	
	}	

	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyNickSql()
	{
	
		StringBuffer sb =  new StringBuffer("select userID, nick, email, password, status, type, ctime, loginCnt, ltime," +
				" online, mtime, remark " +
				" from Users");
		
		sb.append(" where nick like ? ");
				
		return sb.toString();	
	}	

	/**
	 * 返回所有记录数
	 */
	public String getCountSql1()
	{
		return "select count(*) as COUNT from Users";
	}
	
	/**
	 * 返回所有记录数
	 */
	public String getCountSql4()
	{
		return "select count(*) as COUNT from Userdetails";
	}



	/**
	 * 返回根据查询条件的记录数
	 */
	public String getCountSql2()
	{
		return "select count(*) as COUNT from Users where UserID=? ";
	}

	/**
	 * 返回根据查询条件的记录数
	 */
	public String getCountSql3()
	{
		return "select count(*) as COUNT from Users where Nick=? and Email=? and Status=? and Type=? ";
	}
	/**
	 * 返回根据昵称查询条件的记录数
	 */
	public String getCountByNickSql()
	{
		return "select count(*) as COUNT from Users where nick=? ";
	}

	/**
	 * 返回EMAIL查询语句 
	 */
	public String getByEmailSql()
	{
		StringBuffer sb =  new StringBuffer("select userID, nick, email, password, status, type, ctime, loginCnt, ltime," +
				" online, mtime, remark from Users");
		
		sb.append(" where Email=?  Order by Email ");
				
		return sb.toString();
	}
	/**
	 * 返回昵称查询语句 
	 */
	public String getByNickSql()
	{
		StringBuffer sb =  new StringBuffer("select userID, nick, email, password, status, type, ctime, loginCnt, ltime," +
				" online, mtime, remark from Users ");
		
		sb.append(" where nick=?  Order by nick ");
				
		return sb.toString();
	}

	
	/**
	 * @see net.comm.job.db.iServiceSql.impl.UserServiceSql#getMaxInputsForSelectWhereInQueries()
	 */
	public int getMaxInputsForSelectWhereInQueries() {
		// For Oracle, the maximum supported number of expressions in a list is 1000. 
		return 1000;
	}
	
	/**
	 * 返回根据会员ID查询个性标签语句 
	 */
	public String getinterestByUserIDSql()
	{
		StringBuffer sb =  new StringBuffer("select Interest,InterestSet from Userdetails");
		
		sb.append(" where UserID=? ");
				
		return sb.toString();
	}
}
